﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Threading.Tasks;
using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;



namespace King.Util
{
    /// <summary>
    /// Excel通用程序组件
    /// </summary>
    public class CUtilExcel
    {
        #region Err

        #endregion

        #region Const

        #endregion

        #region Public Function

        /// <summary>
        /// 将Excel文件内容读取到数据表中
        /// </summary>
        /// <param name="strExcelFilePath">Excel文件路径</param>
        /// <param name="strSheetName">sheet页名称</param>
        /// <returns>数据表对象</returns>
        public static DataTable ExcelToDataTable(string strExcelFilePath,string strSheetName)
        {
            var oRetTable = new DataTable();

            using(var oFileStream=new FileStream(strExcelFilePath,FileMode.Open))
            {
                var oRetWorkBook = new HSSFWorkbook(oFileStream);
                var oRetSheet = oRetWorkBook.GetSheet(strSheetName);
                var oHeaderRow = oRetSheet.GetRow(0);
                int nCellCount = oHeaderRow.Cells.Count();
                int nRowCount = oRetSheet.LastRowNum;
                for (int nCellIndex = oHeaderRow.FirstCellNum; nCellIndex < nCellCount; nCellIndex++)
                {
                    try
                    {
                        var oRetColumn = new DataColumn(oHeaderRow.GetCell(nCellIndex).StringCellValue.Trim());
                        oRetTable.Columns.Add(oRetColumn);
                    }
                    catch(Exception ex)
                    {
                        throw new Exception(string.Format("解析出错列索引：{0}; 出错原因：{1}",nCellIndex,ex.Message));
                    }
                }
                for (int nRowIndex = oRetSheet.FirstRowNum + 1; nRowIndex <= nRowCount; nRowIndex++)
                {
                    try
                    {
                        var oHssfRow = oRetSheet.GetRow(nRowIndex);
                        var oRetRow = oRetTable.NewRow();
                        for (int nCellIndex = oHssfRow.FirstCellNum; nCellIndex < nCellCount; nCellIndex++)
                        {
                            if (oHssfRow.GetCell(nCellIndex) != null)
                                oRetRow[nCellIndex] = oHssfRow.GetCell(nCellIndex).StringCellValue.Trim();
                        }
                        oRetTable.Rows.Add(oRetTable);
                    }
                    catch(Exception ex)
                    {
                        throw new Exception(string.Format("解析出错行索引：{0}; 出错原因：{1}", nRowIndex, ex.Message));

                    }                 
                }
            }

            return oRetTable;
        }

        #endregion

        #region 辅助方法

        #endregion
     
    }
}
